# coding:utf-8

"""
CopyRight(c)  2017-09-15  Fraplus Corp

Author: Xiaodong.yang
Email:  xiaodong.yang@unidt.com
Functions:

import data from excel files

dependence package:

"""
import json
import pandas as pd
import pymongo

from UploadSurvey import get_db_client
import fralog.FraLog as FraLog

#
# 日志
logger = FraLog.Fralog().getFileLog()

db, client = get_db_client(dbname='test')


def main():
    # collection_name = 'questions_collection'
    # excel = pd.read_csv('questions.csv', encoding='gbk')# pd.read_excel('questions.csv', sheetname=0)
    #
    # excel = excel.to_json(orient='records')
    # excel = json.loads(excel)
    # db.questions_collection.remove()
    # db.questions_collection.insert(excel)
    # export_questions()
    pass


def update_all_questions(filename):
    '''
    重新导入问题库，该方法会删除数据库中原有的数据
    :param filename: 
    :return: 
    '''
    excel = pd.read_excel(filename)

    excel = excel.to_json(orient='records')
    excels = json.loads(excel)
    data = []
    colums = ['beta_O', 'beta_C', 'beta_E', 'beta_A', 'beta_N']
    for excel in excels:
        if excel['question_type'] != 2:
            for col in colums:
                if excel.get(col) is None:
                    logger.debug('列 %s 为空， 补 0' % (col))
                    excel[col] = 0
            logger.debug(excel)
        data.append(excel)
    db.questions_collection.remove()
    db.questions_collection.insert(data)


def add_question(quest):
    '''
    添加一条问题,不删除原有数据
    :param quest: 
    :return: 
    '''
    db.questions_collection.insert(quest)


def modify_type(src_code, type):
    '''
    更改某一问卷的类型， 访问codetype
    :param src_code: 
    :param type: 
    :return: 
    '''
    rows = db.codetype.find({'code': src_code})
    for row in rows:
        row['survey_type'] = type
    pass


def export_collection(collection='questions_collection', filename='questions-now.xlsx'):
    '''
    导出问题库
    :return: 
    '''
    rows = db[collection].find()

    ret = []
    for row in rows:
        del row['_id']
        ret.append(row)
    df = pd.DataFrame(ret)
    # df.to_csv('surveys-now.csv')
    df.to_excel(filename, header=True)


def import_surveys(filename):
    '''

    导入问卷： 删除现有数据，插入文件中的数据
    :param filename:  包含问卷信息的excel文件名 
    :return: 
    '''
    excel = pd.read_excel(filename, sheetname=0)
    excel = excel.to_json(orient='records')
    excel = json.loads(excel)
    # db.surveys_collection.remove()
    db.surveys_collection.insert(excel)
    pass


def import_excel_to_collection(filename='', sheet=0, dest=''):
    collection = db[dest]
    collection.remove()

    excel = pd.read_excel(filename, sheetname=sheet)
    excel = excel.to_json(orient='records')
    excel = json.loads(excel)
    collection.insert(excel)


def add_survey(code, questions):
    pass


def insert_surveys_collection(filename):
    '''
    新增问卷资料
    :param filename:
    :return:
    '''
    excel = pd.read_excel(filename, encoding='gbk', sheetname=0)

    excel = excel.to_json(orient='records')
    excel = json.loads(excel)
    print(excel)
    # db.surveys_collection.remove()
    db.surveys_collection.insert(excel)


def insert_questions_collection(filename):
    '''
    新增问卷明细
    :param filename:
    :return:
    '''
    excel = pd.read_excel(filename, encoding='gbk', sheetname='questions')

    excel = excel.to_json(orient='records')
    excel = json.loads(excel)
    print(excel)
    # db.questions_collection.remove()
    db.questions_collection.insert(excel)


def insert_code_type(survey_code='guxiaofan', type='custom', report_code=None):
    ret = {}
    ret['code'] = survey_code
    ret['src_collection'] = 'desc_collection'
    ret['survey_type'] = type
    ret['report_code'] = report_code
    db.codetype.insert(ret)


if __name__ == '__main__':
    # export_collection(collection = 'questions_collection', filename = 'questions-2018-01-05.xlsx')
    # insert_code_type(survey_code='python', type='custom', report_code=None)
    # insert_code_type(survey_code='fra-hr', type='email', report_code='fra-hr')
    update_all_questions('quesions999.xlsx')
    # logger.info('数据导入完毕')
    # main()
    # insert_surveys_collection('surveys_latest.xlsx')
    # insert_questions_collection('questions-2017-12-29.xlsx')
    # export_questions()
    # import_surveys('surveys-now_HR items added.xlsx')
    # import_excel_to_collection(filename='president.xlsx', dest='president')


